package gov.nhso.prov.acc.apps.acc6;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;
import com.itextpdf.text.Chunk;
import com.itextpdf.text.Document;
import com.itextpdf.text.Font;
import com.itextpdf.text.Image;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfWriter;

import gov.nhso.prov.acc.beans.Acc67ExpenseCategoryBean;
import gov.nhso.prov.acc.beans.Acc6BaacHolidayBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseTransferBean;
import gov.nhso.prov.acc.beans.Acc6ProvinceAccountBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.beans.Acc6VenderBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings({"rawtypes","deprecation","static-access"})
public class ACC60301 extends Utility{
	
	public String url = "/apps/acc6/ACC60301A.jsp";
	public String path = "";
	
	public ACC60301(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.clearLastRequest(request, "lastSearch");
			this.init(request);
		}else if(job.equals("add")){
			this.add(request);
			this.getHoliDay(request);
		}else if(job.equals("save")){
			this.save(request);
		}else if(job.equals("viewEdit")){
			this.viewEdit(request);
			this.getHoliDay(request);
		}else if(job.equals("search")){
			this.setLastRequest(request, "lastSearch");
			this.search(request);
		}else if(job.equals("edit")){
			this.edit(request);
		}else if(job.equals("back")){
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		}else if(job.equals("del")){
			this.del(request);
		}else if(job.equals("confirm")){
			this.confirm(request);
		}else if(job.equals("view")){
			this.view(request);
		}else if(job.equals("export")){
			this.export(request, response);
		}else if(job.equals("print")){
			this.print(request, response);
		}else if(job.equals("print2")){
			this.print2(request, response);
		}else if(job.equals("downloadTemplateTrans")){
			this.downloadTemplateTrans(request, response);
		}else if(job.equals("deleteTrn")){
			this.deleteTrn(request);
		}
	}
	
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("job", "search");
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String add(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			/*Acc6VenderBean venderBean = new Acc6VenderBean();
			List listVender = null;*/
			List listCategory = null;
			
			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			listCategory = dao.selectList(expenseCategoryBean);
			/*venderBean.setProvinceId(this.user(request).getAreaCode());
			listVender = dao.selectList(venderBean, " AND VENDER_TYPE <> 'C' AND VENDER_ACCOUNT IS NOT NULL");*/

			request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
			request.setAttribute("listCategory", listCategory);
			//request.setAttribute("listVender", listVender);
			//request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(this.dateUtil().getBudgetYear()));
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("docNo", "สร้างจากระบบ");
			request.setAttribute("job", "save");
			request.setAttribute("count", "0");
			request.setAttribute("display", "none");
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60301B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String save(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			int i=0; 
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String expenseDate = this.getAttr(request, "expenseDate");
			String expenseSubject = this.getAttr(request, "expenseSubject");
			String expenseDesc = this.getAttr(request, "expenseDesc");
			String docNo = this.genCode(dao).getExpenseDocNo(budgetYear,provinceId,"T");
			
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setExpenseType("T");
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
			expenseBean.setProvinceId(provinceId);
			expenseBean.setExpenseSubject(expenseSubject);
			expenseBean.setExpenseDesc(expenseDesc);
			expenseBean.setCreatedBy(this.user(request).getUserId());
			expenseBean.setUpdatedBy(this.user(request).getUserId());
			
			dao.insert(expenseBean);
			
			String[] refNo = request.getParameterValues("refNo");
			String[] allocate = request.getParameterValues("allocate");
			String[] vander = request.getParameterValues("vander");
			String[] transferAmount = request.getParameterValues("transferAmount");
			String[] responsibleName = request.getParameterValues("responsibleName");
			String[] category = request.getParameterValues("category");
			String[] transferDesc = request.getParameterValues("transferDesc");
			String[] isBorrow = request.getParameterValues("isBorrow");
			
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			for(i=0;i<refNo.length;i++){
				expenseTransferBean = new Acc6ExpenseTransferBean();
				expenseTransferBean.setDocNo(docNo);
				expenseTransferBean.setRefNo(this.encodeUTF(refNo[i]));
				expenseTransferBean.setAllocateCode(allocate[i]);
				expenseTransferBean.setVenderCode(vander[i]);
				expenseTransferBean.setTransferAmount(transferAmount[i].replace(",", ""));
				expenseTransferBean.setResponsibleName(this.encodeUTF(responsibleName[i]));
				expenseTransferBean.setCategoryId(category[i]);
				expenseTransferBean.setTransferDesc(this.encodeUTF(transferDesc[i]));
				expenseTransferBean.setTransferSeq(String.valueOf(i+1));
				expenseTransferBean.setIsBorrow(isBorrow[i]);
				expenseTransferBean.setCreatedBy(this.user(request).getUserId());
				expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
				dao.insert(expenseTransferBean);
			}
			
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("docNo", docNo);
			request.setAttribute("count", i);
			request.setAttribute("mode", "1");
			this.message(request).setMsgCode("I002");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	@SuppressWarnings("unchecked")
	private String viewEdit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			List listCategory = new ArrayList();
			List listExpenseTransfer = new ArrayList();
			List listVender = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			Acc6VenderBean venderBean = new Acc6VenderBean(); 
			
			expenseCategoryBean.setCategoryAcc("6");
			listCategory = dao.selectList(expenseCategoryBean);

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			String mode = this.getAttr(request, "mode");
			//mode 1 = แก้ไขรายการที่เป็นเตรียมรายการ
			//mode 2 = แก้ไขรายการที่เป็นสำเร็จ และ สำเร็จบ้างส่วน
			expenseBean.setDocNo(docNo);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setProvinceId(provinceId);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			String sql = "";
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			expenseTransferBean.setDocNo(docNo);
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			listExpenseTransfer = dao.selectList(expenseTransferBean, xcols, " ORDER BY TRANSFER_SEQ ASC");
			for (int i = 0; i < listExpenseTransfer.size(); i++) {
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				String allocateCode = bean.getAllocateCode();
				String venderCode = bean.getVenderCode();
				pv = new HashMap();
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
				sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+allocateCode+"'");
				pv = dao.findDataSql(sb.toString());
				
				if (pv.get("ALLOCATE_NAME") == null){
					bean.setAllocateName("");
				}else{
					bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
				}
				if (pv.get("ALLOCATE_NAME_SUB") == null){
					bean.setAllocateSub("");
				}else{
					bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
				}
				
				pv = new HashMap();
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME,VENDER_ACCOUNT ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+venderCode+"'");
				pv = dao.findDataSql(sb.toString());
				
				if (pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				}else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if (pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				}else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				if (pv.get("VENDER_ACCOUNT") == null){
					bean.setVenderAccount("");
				}else{
					bean.setVenderAccount(pv.get("VENDER_ACCOUNT").toString());
				}
				
			}
			
			sql = "SELECT PROVINCE_ID, PROVINCE_NAME FROM " + this.schema + ".PROVINCE WHERE PROVINCE_ID != '"+this.user(request).getAreaCode()+"' AND NHSO_ZONE NOT IN ('14', '15') ORDER BY PROVINCE_NAME ASC";
			request.setAttribute("listAllProvice", dao.listDataSql(sql));
			
			String maxSeq = dao.chkData(dao.findDataSql("SELECT MAX(TRANSFER_SEQ) AS MAX_TRANSFER_SEQ FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER WHERE DOC_No = '"+docNo+"'").get("MAX_TRANSFER_SEQ"));

			venderBean.setProvinceId(provinceId);
			listVender = dao.selectList(venderBean, " AND VENDER_TYPE <> 'C' AND VENDER_ACCOUNT IS NOT NULL");
			Map mapCat = new HashMap();
			for (int i = 0 ; i < listCategory.size() ; i ++) {
				Acc67ExpenseCategoryBean bean = (Acc67ExpenseCategoryBean)listCategory.get(i);
				mapCat.put(bean.getCategoryId(), bean.getCategoryName());
			}
			
			request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(budgetYear));
			request.setAttribute("listVender", listVender);
			request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
			request.setAttribute("listCategory", listCategory);
			request.setAttribute("mapCat", mapCat);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			
			request.setAttribute("job", "edit");
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			request.setAttribute("count", maxSeq);
			request.setAttribute("maxSeq", maxSeq);
			request.setAttribute("docNo", expenseBean.getDocNo());
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("mode", mode);
			request.setAttribute("display", "");
			request.setAttribute("pv", pv);
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60301B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String docNo = this.getAttr(request, "docNo");
			String refNo = this.getAttr(request, "refNo");
			String expenseSubject = this.getAttr(request, "expenseSubject");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String expenseStatus = this.getAttr(request, "expenseStatus");
			String condition = "";

			this.beanUtil().copyRequest2Bean(request, pageBean);
			expenseBean.setDocNo(docNo);
			expenseBean.setExpenseSubject(expenseSubject);
			expenseBean.setActive("Y");
			expenseBean.setProvinceId(provinceId);
			expenseBean.setExpenseType("T");
			expenseBean.setExpenseStatus(expenseStatus);
			expenseBean.setBudgetYear(budgetYear);
			
			if (startDate != null && !startDate.equals("")){
				startDate = this.dateUtil().changeFormatDate(startDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) >= TO_DATE('"+startDate+"','DD/MM/YYYY') ";
			}
			if (endDate != null && !endDate.equals("")){
				endDate = this.dateUtil().changeFormatDate(endDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) <= TO_DATE('"+endDate+"','DD/MM/YYYY') ";
			}
			if (refNo != null && !refNo.equals("")) {
				condition += " AND DOC_NO IN (SELECT DOC_NO FROM ACC6_EXPENSE_TRANSFER WHERE REF_NO LIKE '%" + refNo + "%') ";
			}
			if (this.user(request).getAreaLevel().equals("2")){	
				condition += " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
			}
			pageBean = dao.selectPage(expenseBean, " " + condition,pageBean);
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("startDate", this.getAttr(request, "startDate"));
			request.setAttribute("endDate", this.getAttr(request, "endDate"));
			request.setAttribute("docNo", docNo);
			request.setAttribute("job", "search");
			request.setAttribute("expenseStatusSel", expenseStatus);
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60301A.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String edit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			int i=0;
			String budgetYear = this.getAttr(request, "budgetYear");
			String expenseDate = this.getAttr(request, "expenseDate");
			String provinceId = this.getAttr(request, "provinceId");
			String expenseSubject = this.getAttr(request, "expenseSubject");
			String expenseDesc = this.getAttr(request, "expenseDesc");
			String docNo = this.getAttr(request, "docNo");
			String mode = this.getAttr(request, "mode");
			
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			Acc6TransactionBean atb = new Acc6TransactionBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setExpenseType("T");
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
			expenseBean.setProvinceId(provinceId);
			expenseBean.setExpenseSubject(expenseSubject);
			expenseBean.setExpenseDesc(expenseDesc);
			expenseBean.setUpdatedBy(this.user(request).getUserId());
			expenseBean.setUpdatedDate(this.sysDate);
			
			dao.update(expenseBean);
			
			String[] refNo = request.getParameterValues("refNo");
			String[] allocate = request.getParameterValues("allocate");
			String[] vander = request.getParameterValues("vander");
			String[] transferAmount = request.getParameterValues("transferAmount");
			String[] category = request.getParameterValues("category");
			String[] responsibleName = request.getParameterValues("responsibleName");
			String[] transferDesc = request.getParameterValues("transferDesc");
			String[] isBorrow = request.getParameterValues("isBorrow");
			String[] transferSeq = request.getParameterValues("transferSeq2");
			String[] transactionTimestamp = request.getParameterValues("transactionTimestamp");
			
			if (mode.equals("1")) {
				String sql = "DELETE "+this.schema+".ACC6_EXPENSE_TRANSFER WHERE DOC_NO='"+docNo+"'";
				dao.execDataSql(sql);
			}
			if(transferAmount != null && transferAmount.length > 0){
				for (i=0;i<transferAmount.length;i++) {
					expenseTransferBean = new Acc6ExpenseTransferBean();
					expenseTransferBean.setDocNo(docNo);
					expenseTransferBean.setTransferAmount(dao.chkData(transferAmount[i].replace(",", "")));
					expenseTransferBean.setTransferDesc(this.encodeUTF(transferDesc[i]));
					expenseTransferBean.setRefNo(this.encodeUTF(refNo[i]));
					expenseTransferBean.setAllocateCode(dao.chkData(allocate[i]));
					expenseTransferBean.setVenderCode(dao.chkData(vander[i]));
					expenseTransferBean.setCategoryId(dao.chkData(category[i]));
					expenseTransferBean.setIsBorrow(isBorrow[i]);
					expenseTransferBean.setResponsibleName(this.encodeUTF(responsibleName[i]));
					if (mode.equals("1")) {
						expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
						expenseTransferBean.setCreatedBy(this.user(request).getUserId());
						expenseTransferBean.setTransferSeq(String.valueOf(i+1));
						dao.insert(expenseTransferBean);
					} else {
						expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
						expenseTransferBean.setUpdatedDate(this.sysDate);
						expenseTransferBean.setTransferSeq(transferSeq[i]);
						dao.update(expenseTransferBean);
						
						atb.setAllocateCode(dao.chkData(allocate[i]));
						//atb.setBudgetYear(budgetYear);
						//atb.setProvinceId(provinceId);
						//atb.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
						atb.setRefNo(this.encodeUTF(refNo[i]));
						atb.setCategoryId(dao.chkData(category[i]));
						atb.setVenderCode(dao.chkData(vander[i]));
						atb.setExpenseAmount(dao.chkData(transferAmount[i].replace(",", "")));
						atb.setIsBorrow(isBorrow[i]);
						dao.update(atb, " TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') = '" + transactionTimestamp[i] + "'");
					}
				}
			}
			dao.printMessage();
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("docNo", docNo);
			request.setAttribute("count", i);
			this.message(request).setMsgCode("I002");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			dao.printMessage();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	
	}
	
	private String del(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			String docNo = this.getAttr(request, "docNo");
			
			dao.execDataSql("UPDATE ACC6_EXPENSE_TRANSFER SET ACTIVE = 'N' WHERE DOC_NO = '"+docNo+"'");
			dao.execDataSql("UPDATE ACC6_EXPENSE SET ACTIVE = 'N' WHERE DOC_NO = '"+docNo+"'");
			
			request.setAttribute("docNo", "");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	
	}
	private String confirm(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			List listExpenseTransfer = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			if(docNo.equals("สร้างจากระบบ")){
				docNo = this.genCode(dao).getExpenseDocNo(budgetYear,provinceId,"T");
				String expenseDate = this.getAttr(request, "expenseDate");
				String expenseSubject = this.getAttr(request, "expenseSubject");
				String expenseDesc = this.getAttr(request, "expenseDesc");
				
				expenseBean = new Acc6ExpenseBean();
				expenseBean.setDocNo(docNo);
				expenseBean.setExpenseStatus("A");
				expenseBean.setExpenseType("T");
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
				expenseBean.setProvinceId(provinceId);
				expenseBean.setExpenseSubject(expenseSubject);
				expenseBean.setExpenseDesc(expenseDesc);
				expenseBean.setCreatedBy(this.user(request).getUserId());
				expenseBean.setUpdatedBy(this.user(request).getUserId());
				dao.insert(expenseBean);
				
				String[] refNo = request.getParameterValues("refNo");
				String[] allocate = request.getParameterValues("allocate");
				String[] vander = request.getParameterValues("vander");
				String[] transferAmount = request.getParameterValues("transferAmount");
				String[] responsibleName = request.getParameterValues("responsibleName");
				String[] category = request.getParameterValues("category");
				String[] transferDesc = request.getParameterValues("transferDesc");
				String[] isBorrow = request.getParameterValues("isBorrow");

				if(refNo.length > 0){
					for(int i=0;i<refNo.length;i++){
						expenseTransferBean = new Acc6ExpenseTransferBean();
						expenseTransferBean.setDocNo(docNo);
						expenseTransferBean.setTransferSeq(String.valueOf((i+1)));
						expenseTransferBean.setRefNo(this.encodeUTF(refNo[i]));
						expenseTransferBean.setAllocateCode(allocate[i]);
						expenseTransferBean.setVenderCode(dao.chkData(vander[i]));
						expenseTransferBean.setTransferAmount(dao.chkData(transferAmount[i].replace(",", "")));
						expenseTransferBean.setResponsibleName(this.encodeUTF(responsibleName[i]));
						expenseTransferBean.setCategoryId(dao.chkData(category[i]));
						expenseTransferBean.setTransferDesc(this.encodeUTF(transferDesc[i]));
						expenseTransferBean.setIsBorrow(isBorrow[i]);
						expenseTransferBean.setCreatedBy(this.user(request).getUserId());
						expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
						dao.insert(expenseTransferBean);
					}
				}
				
			}else{
				expenseBean.setDocNo(docNo);
				expenseBean.setProvinceId(provinceId);
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setExpenseStatus("A");
				dao.update(expenseBean);
				String sql = "DELETE "+this.schema+".ACC6_EXPENSE_TRANSFER WHERE DOC_NO='"+docNo+"'";
				
				dao.execDataSql(sql);
				
				String[] refNo = request.getParameterValues("refNo");
				String[] allocate = request.getParameterValues("allocate");
				String[] vander = request.getParameterValues("vander");
				String[] transferAmount = request.getParameterValues("transferAmount");
				String[] category = request.getParameterValues("category");
				String[] responsibleName = request.getParameterValues("responsibleName");
				String[] transferDesc = request.getParameterValues("transferDesc");
				String[] isBorrow = request.getParameterValues("isBorrow");
				
				if(transferAmount.length > 0){
					for(int i=0;i<transferAmount.length;i++){
						expenseTransferBean = new Acc6ExpenseTransferBean();
						expenseTransferBean.setDocNo(docNo);
						expenseTransferBean.setTransferAmount(dao.chkData(transferAmount[i].replace(",", "")));
						expenseTransferBean.setTransferDesc(this.encodeUTF(transferDesc[i]));
						expenseTransferBean.setUpdatedDate(this.sysDate);
						expenseTransferBean.setRefNo(this.encodeUTF(refNo[i]));
						expenseTransferBean.setAllocateCode(dao.chkData(allocate[i]));
						expenseTransferBean.setVenderCode(dao.chkData(vander[i]));
						expenseTransferBean.setCategoryId(dao.chkData(category[i]));
						expenseTransferBean.setResponsibleName(this.encodeUTF(responsibleName[i]));
						expenseTransferBean.setTransferSeq(String.valueOf(i+1));
						expenseTransferBean.setIsBorrow(isBorrow[i]);
						expenseTransferBean.setCreatedBy(this.user(request).getUserId());
						expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
						dao.insert(expenseTransferBean);
					}
				}
			}
			
			
			expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			expenseTransferBean = new Acc6ExpenseTransferBean();
			expenseTransferBean.setDocNo(docNo);
			expenseTransferBean.setActive("Y");
			listExpenseTransfer = dao.selectList(expenseTransferBean," ORDER BY TRANSFER_SEQ ASC");
			for (int i = 0; i < listExpenseTransfer.size(); i++) {
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,NVL(ALLOCATE_NAME,'') AS ALLOCATE_NAME ");
				sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
				pv = dao.findDataSql(sb.toString());
				
				if(pv.get("ALLOCATE_NAME") == null){
					bean.setAllocateName("");
				} else{
					bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
				}
				if(pv.get("ALLOCATE_NAME_SUB") == null){
					bean.setAllocateSub("");
				} else{
					bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
				}
				
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,NVL(VENDER_NAME,'') AS VENDER_NAME,VENDER_ACCOUNT ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"' ");
				pv = dao.findDataSql(sb.toString());
				
				if(pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				} else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if(pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				} else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				if (pv.get("VENDER_ACCOUNT") == null){
					bean.setVenderAccount("");
				}else{
					bean.setVenderAccount(pv.get("VENDER_ACCOUNT").toString());
				}
			}
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("docNo", docNo);
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60301C.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			List listExpenseTransfer = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			
			expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			expenseTransferBean.setDocNo(docNo);
			expenseTransferBean.setActive("Y");
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			listExpenseTransfer = dao.selectList(expenseTransferBean, xcols," ORDER BY TRANSFER_SEQ ASC");
			for (int i = 0; i < listExpenseTransfer.size(); i++) {
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
				sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
				pv = dao.findDataSql(sb.toString());

				if (pv.get("ALLOCATE_NAME") == null){
					bean.setAllocateName("");
				}else{
					bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
				}
				if (pv.get("ALLOCATE_NAME_SUB") == null){
					bean.setAllocateSub("");
				}else{
					bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
				}
				
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME,VENDER_ACCOUNT ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
				pv = dao.findDataSql(sb.toString());

				if (pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				}else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if (pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				}else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				if (pv.get("VENDER_ACCOUNT") == null){
					bean.setVenderAccount("");
				}else{
					bean.setVenderAccount(pv.get("VENDER_ACCOUNT").toString());
				}
				
			}

			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("docNo", docNo);
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60301C.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String export(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			List listExpenseTransfer = new ArrayList();
			Map mapData = new HashMap();
			String venderName = "";
			String venderAccountCode = "";
			
			expenseTransferBean.setDocNo(docNo);
			expenseBean.setDocNo(docNo);
			listExpenseTransfer = dao.selectList(expenseTransferBean);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\""+ docNo + ".xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet(docNo);
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFRow row = sheet.createRow((short)0);
			
			row.setRowStyle(cellStyle);
			sheet.setDefaultColumnWidth((short) 25);
			
			row.createCell((short)0).setCellValue("เอกสารอ้างอิง");
			row.createCell((short)1).setCellValue("รหัสแจ้งจัดสรรเงินให้คู่สัญญา");
			row.createCell((short)2).setCellValue("รหัสหน่วยบริการ/คู่สัญญา");
			row.createCell((short)3).setCellValue("เลขที่บัญชี - ชื่อหน่วยบริการ/คู่สัญญา");
			row.createCell((short)4).setCellValue("จำนวนเงิน");
			row.createCell((short)5).setCellValue("รหัสหมวดการจ่าย");
			row.createCell((short)6).setCellValue("ชื่อหมวดการจ่าย");
			row.createCell((short)7).setCellValue("ชื่อผู้รับผิดชอบ");
			row.createCell((short)8).setCellValue("คำอธิบาย");
			row.createCell((short)9).setCellValue("สถานะเงินยืม");
			
			for (int i=0;i<listExpenseTransfer.size();i++){
				row = sheet.createRow((short)(i+1));
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				mapData = this.dataUtil(dao).getMapVenderDataById(bean.getVenderCode());
				venderName = dao.chkData(mapData.get("VENDER_NAME"));
				venderAccountCode = dao.chkData(mapData.get("VENDER_ACCOUNT"));
				row.createCell((short)0).setCellValue(bean.getRefNo());
				row.createCell((short)1).setCellValue(bean.getAllocateCode());
				row.createCell((short)2).setCellValue(bean.getVenderCode());
				row.createCell((short)3).setCellValue(venderAccountCode.trim() + " - " + venderName);
				row.createCell((short)4).setCellValue(bean.getTransferAmount());
				row.createCell((short)5).setCellValue(dao.chkData(bean.getCategoryId()));
				row.createCell((short)6).setCellValue(this.dataUtil(dao).getCategoryById(dao.chkData(bean.getCategoryId())));
				row.createCell((short)7).setCellValue(bean.getResponsibleName());
				row.createCell((short)8).setCellValue(bean.getTransferDesc());
				row.createCell((short)9).setCellValue(bean.getIsBorrow());
			}
			
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String print(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		File fontFile = new File("THSarabun.ttf");
	    Paragraph preface = new Paragraph();
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			ByteArrayOutputStream outputStream = null;

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
		    //Document document = new Document();
		    Document document = new Document(PageSize.A4, (float) 85.039370079, (float) 56.692913386, (float) 130, 0);
		    StringBuffer sb = new StringBuffer();
		    Map map = new HashMap();
		    Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
		    expenseBean.setDocNo(docNo);
		    expenseBean.setBudgetYear(budgetYear);
		    expenseBean = (Acc6ExpenseBean)dao.select(expenseBean);
		    
		    outputStream = new ByteArrayOutputStream();
		    PdfWriter.getInstance(document, outputStream);
		    Paragraph header = new Paragraph();
		    Paragraph body = new Paragraph();
		    Paragraph footer = new Paragraph();
		    //document.setMargins(65, 50, 130, 0);
		    document.open();
		    BaseFont baseFont = null;
			baseFont = BaseFont.createFont(fontFile.toString() , BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
		    Font font = new Font(baseFont, 16);
		    String ws = "\u00a0\u00a0";
		    String tab = ws+ws+ws+ws;
			String[] address = this.dataUtil(dao).getProvinceAddressById(provinceId).split(" ");
			int l = (int) Math.round(address.length/2);
			String row1 = "";
			String row2 = "";
			for (int o = 0; o < address.length; o++){
				if (o < l+2) {
					row1 += address[o].trim()+ws;
				} else {
					row2 += address[o].trim()+ws;
				}
			}
			
			this.path = request.getRealPath("WEB-INF/classes/krut.jpeg");
		    Image krut = Image.getInstance(this.path);
		    krut.setAbsolutePosition(250f, 708f);
		    krut.scaleAbsolute(85f, 85f);
		    document.add(krut);
		    header.setFont(font);
		    header.setLeading(17f);
		    body.setLeading(22f);
		    header.add("ที่");
		    header.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    header.add("สำนักงานสาธารณสุขจังหวัด"+ws+""+this.dataUtil(dao).getProvinceName(provinceId));
		    header.add(Chunk.NEWLINE);
		    header.add(ws+ws+ws+tab+tab+tab+tab+tab+tab+tab+tab+this.thaiNumber(row1));
		    header.add(Chunk.NEWLINE);
		    header.add(ws+ws+ws+tab+tab+tab+tab+tab+tab+tab+tab+this.thaiNumber(row2));
		    /*header.add(Chunk.NEWLINE);
		    header.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);
		    header.add(Chunk.NEWLINE);
		    header.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab);*/
		    body.setFont(font);
		    body.add(Chunk.NEWLINE);
		    body.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab+ws);
		    body.add("วันที่ ");
		    body.add(Chunk.NEWLINE);
		    body.add("เรื่อง   แจ้งโอนเงิน");
		    body.add(Chunk.NEWLINE);
		    body.add("เรียน  	ผู้จัดการธนาคารเพื่อการเกษตรและสหกรณ์การเกษตร สาขา");
		    body.add(Chunk.NEWLINE);
		    body.add("สิ่งที่ส่งมาด้วย   รายละเอียดประกอบการโอนเงิน");
		    body.add(Chunk.NEWLINE);
		    body.add(tab+tab+ws+ws);
		    
		    sb.append("SELECT  COUNT(DISTINCT VENDER_CODE) AS COUNT_VENDER ,NVL(SUM(TRANSFER_AMOUNT),0) AS SUM_TRANSFER_AMOUNT ");
		    sb.append("FROM ACC6_EXPENSE_TRANSFER ");
		    sb.append("WHERE DOC_NO = '"+docNo+"'");
		    map = dao.findDataSql(sb.toString());

		    body.add("ด้วยสำนักงานสาธารณสุขจังหวัด " + this.dataUtil(dao).getProvinceName(provinceId)+" มีความประสงค์จะโอนเงินเข้าบัญชีให้กับหน่วย บริการ/คู่สัญญา  จำนวน "+this.thaiNumber(map.get("COUNT_VENDER").toString()));

	    	String sumTransferAmount = "";
	    	sumTransferAmount = dao.chkData(map.get("SUM_TRANSFER_AMOUNT"));
	    	if (sumTransferAmount.substring(0, 1).equals(".")) {
	    		sumTransferAmount = "0"+sumTransferAmount;
	    	}
	    	
		    body.add(" หน่วย 	จำนวนเงิน  "+this.thaiNumber(this.digitUtil().numberFormat("#,##0.00", sumTransferAmount))+" บาท  ("+this.bathThaiText().toThaiMoney(sumTransferAmount)+")");
		    body.add(Chunk.NEWLINE);
		    body.add(tab+tab+ws+ws);
		    body.add(" โดย ");
		    body.add("ตัดจ่ายจากบัญชี\"เงินกองทุนหลักประกันสุขภาพแห่งชาติ สาขาจังหวัด " + this.dataUtil(dao).getProvinceName(provinceId));
		    
		    Acc6ProvinceAccountBean provinceAccountBean = new Acc6ProvinceAccountBean();
		    provinceAccountBean.setProvinceId(provinceId);
		    provinceAccountBean = (Acc6ProvinceAccountBean)dao.select(provinceAccountBean);
		    
		    body.add("\" (บัญชี ๖)  เลขที่บัญชี  "+this.thaiNumber(provinceAccountBean.getProvinceAccountSaving()));
		    body.add(" รายละเอียดตามเอกสารที่ส่งมาพร้อมนี้");
		    body.add(Chunk.NEWLINE);
		    body.add(tab+tab+ws+ws);
		    body.add("จึงเรียนมาเพื่อโปรดพิจารณาและดำเนินการต่อไปด้วย จะเป็นพระคุณ");
		    footer.setFont(font);
		    footer.setLeading(18f);
		    footer.add(Chunk.NEWLINE);
		    footer.add(ws+ws+tab+tab+tab+tab+tab+tab+tab+tab+ws);
		    footer.add("ขอแสดงความนับถือ");

		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    footer.add(Chunk.NEWLINE);
		    
			footer.add("กลุ่มงาน " + dao.chkData(provinceAccountBean.getGroupWork()));
		    footer.add(Chunk.NEWLINE);
		    footer.add("โทร. " + this.thaiNumber(dao.chkData(provinceAccountBean.getTelephoneNumber())));
		    footer.add(Chunk.NEWLINE);
		    footer.add("โทรสาร. " + this.thaiNumber(dao.chkData(provinceAccountBean.getFaxNumber())));
		    
			preface.add(header);
			preface.add(body);
			preface.add(footer);
			document.add(preface);
		    document.newPage();
		    document.close();
			
			ByteArrayOutputStream baos = outputStream;
			response.setHeader("Expires", "0"); 
	        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); 
	        response.setHeader("Pragma", "public"); 
	        response.setContentType("application/pdf");
	        response.setHeader("Content-Disposition", "attachment; filename="+docNo+".pdf");
			response.setCharacterEncoding("windows-874");
	        response.setContentLength(baos.size()); 
	        ServletOutputStream out;
			
			out = response. getOutputStream();
	        baos.writeTo(out); 
	        out.flush(); 
	        out.close(); 
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String print2(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		File fontFile = new File("THSarabun.ttf");
	    Paragraph preface = new Paragraph();
	    String buffer = "";
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			ByteArrayOutputStream outputStream = null;

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
		    Document document = new Document(PageSize.A4, (float) 84.96, (float) 22.32, (float) 48, 0);
		    //Document document = new Document(PageSize.A4, (float) 85.039370079, (float) 56.692913386, (float) 42.519685039, 0);
		    StringBuffer sb = new StringBuffer();
		    String str = "";
		    Map map = new HashMap();
		    List list = new ArrayList();
			double expenseAmt = 0;
			double incomeAnt = 0;
		    outputStream = new ByteArrayOutputStream();
		    PdfWriter.getInstance(document, outputStream);
		    Paragraph paragraph = new Paragraph();
		    Paragraph headerParagraph = new Paragraph();
		    Paragraph bodyParagraph = new Paragraph();
		    Paragraph bodyParagraph1 = new Paragraph();
		    Paragraph bodyParagraph2 = new Paragraph();
		    Paragraph footerParagraph = new Paragraph();
		    Paragraph newLine = new Paragraph();
		    
		    newLine.add(Chunk.NEWLINE);
		    newLine.setLeading(12f);
		    //document.setMargins(65, 50, 105, 0);
		    document.open();
		    BaseFont baseFont = null;
			baseFont = BaseFont.createFont(fontFile.toString() , BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
		    Font font = new Font(baseFont, 16);
		    Font headFont = new Font(baseFont, 18, Font.BOLD);
		    Font headFontDetail = new Font(baseFont, 16);
		    Font headerFont = new Font(baseFont, 24, Font.BOLD);
		    String ws = "\u00a0\u00a0";
		    String tab = ws+ws+ws+ws;
			String expenseDateMinus = "";
			String groupWork = "";
		    sb.delete(0, sb.length());
		    sb.append("SELECT  COUNT(DISTINCT AET.VENDER_CODE) AS COUNT_VENDER, ");
		    sb.append("TO_NUMBER(SUM(AET.TRANSFER_AMOUNT)) AS SUM_TRANSFER_AMOUNT, ");
		    sb.append("AE.CREATED_DATE -1 AS CREATED_DATE_MINUS,AE.CREATED_DATE,AE.CREATED_DATE + 1 AS CREATED_DATE_PLUS ");
		    sb.append("FROM ACC6_EXPENSE_TRANSFER AET,ACC6_EXPENSE AE ");
		    sb.append("WHERE AET.DOC_NO = '"+docNo+"' AND AE.DOC_NO = AET.DOC_NO AND AE.ACTIVE ='Y' GROUP BY AE.CREATED_DATE");
		    map = dao.findDataSql(sb.toString());
		    
		    String sumTransferAmount = "";
		    if(map.get("SUM_TRANSFER_AMOUNT").toString()== null || map.get("SUM_TRANSFER_AMOUNT").toString().equals("")){
		    	sumTransferAmount = "0";
		    }else{
		    	sumTransferAmount = map.get("SUM_TRANSFER_AMOUNT").toString();
		    	if (sumTransferAmount.substring(0, 1).equals(".")) {
		    		sumTransferAmount = "0"+sumTransferAmount;
		    	}
		    }
		    Acc6ProvinceAccountBean provinceAccountBean = new Acc6ProvinceAccountBean();
		    provinceAccountBean.setProvinceId(provinceId);
		    provinceAccountBean = (Acc6ProvinceAccountBean)dao.select(provinceAccountBean);
			this.path = request.getRealPath("WEB-INF/classes/krut.jpeg");
		    Image krut = Image.getInstance(this.path);
		    krut.setAbsolutePosition(85f, 770f);
		    krut.scaleAbsolute(40f, 40f);
		    document.add(krut);
		    headerParagraph.setFont(headerFont);
		    headerParagraph.add(tab+tab+tab+tab);
		    headerParagraph.add("บันทึกข้อความ");
		    headerParagraph.add(Chunk.NEWLINE);
		    headerParagraph.setFont(headFont);
		    headerParagraph.setLeading(21f);
		    headerParagraph.add(Chunk.NEWLINE);
		    if (dao.chkData(provinceAccountBean.getGroupWork()).equals("")){
		    	headerParagraph.add("ส่วนราชการ "+ws+ws+ws+tab+tab+tab+tab);
		    } else {
		    	groupWork = provinceAccountBean.getGroupWork().replace("กลุ่มงาน", "");
		    	headerParagraph.add("ส่วนราชการ ");
		    	headerParagraph.setFont(headFontDetail);
		    	headerParagraph.add(" กลุ่มงาน " + groupWork);
		    }
		    headerParagraph.setFont(font);
		    headerParagraph.add(" สำนักงานสาธารณสุขจังหวัด  ");
	    	headerParagraph.setFont(headFontDetail);
	    	headerParagraph.add(this.dataUtil(dao).getProvinceName(provinceId));
		    headerParagraph.add(Chunk.NEWLINE);
		    headerParagraph.setFont(headFont);
		    headerParagraph.add("ที่");
		    headerParagraph.add(tab+tab+tab+tab+tab+ws+ws+ws+ws+ws+ws);
		    //expenseDate = this.dateUtil().changeFormatFullDate(this.dateUtil().convertDate("DP", map.get("CREATED_DATE").toString()), "/", " ");
		    expenseDateMinus = this.dateUtil().changeFormatFullDate(this.dateUtil().convertDate("DP", map.get("CREATED_DATE_MINUS").toString()), "/", " ");
		    headerParagraph.add("วันที่ ");
		    headerParagraph.add(Chunk.NEWLINE);
		    
		    sb.delete(0, sb.length());
			sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) AS SUM_INCOMEAMT ");
			sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
			sb.append("WHERE PROVINCE_ID  = '"+provinceId+"' AND REASON_CODE = '00' ");
			sb.append("AND INCOME_DATE <= TO_DATE('"+this.dateUtil().changeDateFromDB(map.get("CREATED_DATE_MINUS").toString(), "")+"235959','DDMMYYYYHH24MISS') ");
			incomeAnt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_INCOMEAMT").toString());
			sb.delete(0, sb.length());
			sb.append("SELECT (NVL(SUM(EXPENSE_AMOUNT), 0)+NVL(SUM(TAX_VALUE), 0)) AS SUM_EXPENSEAMT ");
			sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
			sb.append("WHERE PROVINCE_ID  = '"+provinceId+"' AND REASON_CODE = '00' ");
			sb.append("AND EXPENSE_DATE <= TO_DATE('"+this.dateUtil().changeDateFromDB(map.get("CREATED_DATE_MINUS").toString(), "")+"235959','DDMMYYYYHH24MISS') ");
			expenseAmt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_EXPENSEAMT").toString());
			double result = incomeAnt - expenseAmt;
		    
		    sb.delete(0, sb.length());
		    sb.append("SELECT DISTINCT AC.ALLOCATE_NAME ");
		    sb.append("FROM ACC6_ALLOCATE AC,ACC6_EXPENSE_TRANSFER AET ");
		    sb.append("WHERE AET.DOC_NO = '"+docNo+"' AND AET.ALLOCATE_CODE = AC.ALLOCATE_CODE AND AC.ACTIVE = 'Y'");
		    list = dao.listDataSql(sb.toString());
		    
		    for(int i=0;i<list.size();i++){
		    	map = (Map)list.get(i);
		    	str += map.get("ALLOCATE_NAME").toString()+" , ";
		    }
		    
		    str = str.substring(0,str.length()-2);
		    
		    headerParagraph.add("เรื่อง   	 ");
	    	headerParagraph.setFont(headFontDetail);
	    	headerParagraph.add("ขออนุมัติโอนเงิน"+str);
		    headerParagraph.add( "ปีงบประมาณ พ.ศ. "+this.thaiNumber(budgetYear));
		    headerParagraph.setFont(font);
		    headerParagraph.add("_________________________________________________________________________________");
		    
		    bodyParagraph.setFont(font);
		    bodyParagraph.add("เรียน  	นายแพทย์สาธารณสุขจังหวัด " + this.dataUtil(dao).getProvinceName(provinceId));
		    /*paragraph.add("สิ่งที่ส่งมาด้วย   ข้อมูลรายละเอียดการโอนเงินให้หน่วยบริการ/คู่สัญญา");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);*/
		    bodyParagraph1.setFont(font);
		    bodyParagraph1.add(tab+tab+ws+ws);
		    bodyParagraph1.add(" ด้วยสำนักงานสาธารณสุขจังหวัด " + this.dataUtil(dao).getProvinceName(provinceId));
		    bodyParagraph1.add(" ได้รับจัดสรรเงินและโอนเงินกองทุนงบ  " + str);
		    bodyParagraph1.add(" ปีงบประมาณ พ.ศ. "+ this.thaiNumber(budgetYear) );
		    bodyParagraph1.add(" จากสำนักงานหลักประกันสุขภาพแห่งชาติ เพื่อโอนให้หน่วยบริการต่อไป ");

		    bodyParagraph2.setFont(font);
		    bodyParagraph2.add(tab+tab+ws+ws);
		    if (dao.chkData(provinceAccountBean.getGroupWork()).equals("")){
		    	bodyParagraph2.add("กลุ่มงาน "+ws+ws+ws+tab+tab+tab+tab);
		    } else {
		    	groupWork = provinceAccountBean.getGroupWork().replace("กลุ่มงาน", "");
		    	bodyParagraph2.add("กลุ่มงาน "+groupWork);
		    }
		    
		    bodyParagraph2.add(" ขอโอนเงินกองทุน "+str);
		    bodyParagraph2.add("จากบัญชี \"เงินกองทุนหลักประกันสุขภาพแห่งชาติ สาขาจังหวัด "+ this.dataUtil(dao).getProvinceName(provinceId));
		    
		    bodyParagraph2.add("\"(บัญชี  ๖)  เลขที่บัญชี  " + this.thaiNumber(provinceAccountBean.getProvinceAccountSaving()));
		    bodyParagraph2.add(" ให้หน่วยบริการ/คู่สัญญา ตามสิ่งที่ส่งมาด้วยเป็นจำนวนเงิน "+ this.thaiNumber(this.digitUtil().numberFormat("#,##0.00", sumTransferAmount)));
		    bodyParagraph2.add("บาท ("+ this.bathThaiText().toThaiMoney(sumTransferAmount)+")");
		    
		    footerParagraph.setFont(font);
		    footerParagraph.add(tab+tab+ws+ws);
		    footerParagraph.add("จึงเรียนมาเพื่อโปรดพิจารณาลงนามอนุมัติ");

		    paragraph.setFont(font);
		    paragraph.setLeading(20f);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("สถานะการเงิน ณ วันที่ "+this.thaiNumber(expenseDateMinus));
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("เงินบัญชี ๖ สสจ. "+ this.user(request).getAreaCodeName());
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("เงินในบัญชี   " + this.thaiNumber(this.digitUtil().formatDecimal(result, 2))+" บาท ");
		    paragraph.add(Chunk.NEWLINE);
		    sb.delete(0, sb.length());
		    sb.append("SELECT SUM(AET.TRANSFER_AMOUNT) AS RESULTS ,TO_CHAR(ACE.CREATED_DATE,'YYYY-MM-DD') AS CREATED_DATE ");
		    sb.append("FROM ACC6_EXPENSE_TRANSFER AET,ACC6_EXPENSE ACE ");
		    sb.append("WHERE ACE.DOC_NO = AET.DOC_NO AND ACE.DOC_NO = '"+docNo+"' AND ACE.ACTIVE ='Y' ");
		    sb.append("GROUP BY ACE.CREATED_DATE ");
		    map = dao.findDataSql(sb.toString());
		    paragraph.add("เบิกจ่าย        " +this.thaiNumber(this.digitUtil().numberFormat("#,##0.00",map.get("RESULTS").toString()))+ " บาท ");
		    paragraph.add(Chunk.NEWLINE);
		    if(map.get("CREATED_DATE") != null && !map.get("CREATED_DATE").toString().equals("")){
		    	buffer = map.get("CREATED_DATE").toString();
		    }
		    sb.delete(0, sb.length());
		    sb.append("SELECT NVL(SUM(AET.TRANSFER_AMOUNT),0) AS SUM_TRANSFER ");
		    sb.append("FROM ACC6_EXPENSE_TRANSFER AET,ACC6_EXPENSE ACE ");
		    sb.append("WHERE ACE.DOC_NO = AET.DOC_NO AND ACE.PROVINCE_ID = '" + provinceId + "' ");
		    sb.append("AND TO_DATE('"+buffer+"','YYYY-MM-DD') = TO_DATE(ACE.CREATED_DATE) AND ACE.ACTIVE ='Y' ");
		    map = dao.findDataSql(sb.toString());
		    double sumTtansfer = 0d;
		    if(map.get("SUM_TRANSFER").toString() != null && !map.get("SUM_TRANSFER").toString().equals("")){
		    	sumTtansfer = Double.parseDouble(map.get("SUM_TRANSFER").toString());
		    }
		    
		    sb.delete(0, sb.length());
		    sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS SUM_EXPENSE ");
		    sb.append("FROM ACC6_EXPENSE_OTHER ");
		    sb.append("WHERE PROVINCE_ID = '" + provinceId + "' ");
		    sb.append("AND TO_DATE('"+buffer+"','YYYY-MM-DD') = TO_DATE(CREATED_DATE) AND ACTIVE ='Y' ");
		    map = dao.findDataSql(sb.toString());
		    if(map.get("SUM_EXPENSE").toString() != null && !map.get("SUM_EXPENSE").toString().equals("")){
		    	sumTtansfer += Double.parseDouble(map.get("SUM_EXPENSE").toString());
		    }
		    
		    sb.delete(0, sb.length());
		    sb.append("SELECT NVL(SUM(AEC.CHEQUE_AMOUNT),0) AS SUM_CHEQUE ");
		    sb.append("FROM ACC6_EXPENSE_CHEQUE AEC,ACC6_EXPENSE ACE ");
		    sb.append("WHERE ACE.DOC_NO = AEC.DOC_NO AND ACE.PROVINCE_ID = '" + provinceId + "' ");
		    sb.append("AND TO_DATE('"+buffer+"','YYYY-MM-DD') = TO_DATE(AEC.CREATED_DATE) AND ACE.ACTIVE ='Y' ");
		    map = dao.findDataSql(sb.toString());
		    
		    double sumCheque = 0d;
		    if(map.get("SUM_CHEQUE").toString() != null && !map.get("SUM_CHEQUE").toString().equals("")){
		    	sumCheque = Double.parseDouble(map.get("SUM_CHEQUE").toString());
		    }
		    
		    sb.delete(0, sb.length());
		    sb.append("SELECT SUM_CHEQUE_AMOUNT + SUM_TAX_VALUE AS SUM_CHEQUE_AMOUNT ");
		    sb.append("FROM "+this.schema+".ACC6_CHEQUE ");
		    sb.append("WHERE TO_DATE('"+buffer+"','YYYY-MM-DD') = TO_DATE(CREATED_DATE) AND ACTIVE ='Y' ");
		    sb.append("AND PROVINCE_ID = '" + provinceId + "'");
		    map = dao.findDataSql(sb.toString());
		    
		    if (!dao.chkData(map.get("SUM_CHEQUE_AMOUNT")).equals("")) {
		    	sumCheque += Double.parseDouble(map.get("SUM_CHEQUE_AMOUNT").toString());
		    }
		    double remain = result - (sumTtansfer+sumCheque);
		    paragraph.add("เบิกจ่ายสะสม "+this.thaiNumber(this.digitUtil().formatDecimal((sumCheque+sumTtansfer),2))+" บาท ");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add("คงเหลือ "+this.thaiNumber(this.digitUtil().formatDecimal(remain,2))+ " บาท ");
		    paragraph.add(Chunk.NEWLINE);
		    paragraph.add(tab+tab+tab+ws+ws);
		    paragraph.add("ตรวจสอบถูกต้อง");
		    
			preface.add(headerParagraph);
			preface.add(newLine);
			preface.add(bodyParagraph);
			preface.add(newLine);
			preface.add(bodyParagraph1);
			preface.add(newLine);
			preface.add(bodyParagraph2);
			preface.add(newLine);
			preface.add(footerParagraph);
			preface.add(newLine);
			//preface.add(paragraph);
			document.add(preface);
		    document.newPage();
		    document.close();
			ByteArrayOutputStream baos = outputStream;
			response.setHeader("Expires", "0"); 
	        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); 
	        response.setHeader("Pragma", "public"); 
	        response.setContentType("application/pdf"); 
	        response.setHeader("Content-Disposition", "attachment; filename="+docNo+".pdf");
	        response.setContentLength(baos.size()); 
	        ServletOutputStream out;
			out = response.getOutputStream();
	        baos.writeTo(out);
	        out.flush(); 
	        out.close(); 
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String downloadTemplateTrans(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\"templateTransfer.xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("templateTrans");
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFRow row = sheet.createRow((short)0);
			
			row.setRowStyle(cellStyle);
			sheet.setDefaultColumnWidth((short) 25);
			row.createCell((short)0).setCellStyle(cellStyle);
			row.createCell((short)1).setCellStyle(cellStyle);
			row.createCell((short)2).setCellStyle(cellStyle);
			row.createCell((short)3).setCellStyle(cellStyle);
			row.createCell((short)4).setCellStyle(cellStyle);
			row.createCell((short)5).setCellStyle(cellStyle);
			row.createCell((short)6).setCellStyle(cellStyle);
			row.createCell((short)7).setCellStyle(cellStyle);
			row.createCell((short)8).setCellStyle(cellStyle);
			row.createCell((short)9).setCellStyle(cellStyle);
			
			row.createCell((short)0).setCellValue("เอกสารอ้างอิง");
			row.createCell((short)1).setCellValue("รหัสแจ้งจัดสรรเงินให้คู่สัญญา");
			row.createCell((short)2).setCellValue("รหัสหน่วยบริการ/คู่สัญญา");
			row.createCell((short)3).setCellValue("ชื่อหน่วยบริการ/คู่สัญญา");
			row.createCell((short)4).setCellValue("จำนวนเงิน");
			row.createCell((short)5).setCellValue("รหัสหมวดการจ่าย");
			row.createCell((short)6).setCellValue("ชื่อหมวดการจ่าย");
			row.createCell((short)7).setCellValue("ชื่อผู้รับผิดชอบ");
			row.createCell((short)8).setCellValue("คำอธิบาย");
			row.createCell((short)9).setCellValue("สถานะเงินยืม");
			
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url = "";
	}
	private String deleteTrn(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6ExpenseTransferBean aetb = new Acc6ExpenseTransferBean();
			this.beanUtil().copyRequest2Bean(request, aetb);
			dao.delete(aetb);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private void getHoliDay(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			Acc6BaacHolidayBean bhb = new Acc6BaacHolidayBean();
			List listHoliday = null;
			String strArrHoliday = "";
			listHoliday = dao.selectList(bhb);
			if (listHoliday.size() > 0) {
				strArrHoliday += "[";
				for (int i = 0 ; i < listHoliday.size() ; i++ ) {
					Acc6BaacHolidayBean bean = (Acc6BaacHolidayBean) listHoliday.get(i);
					if (i > 0) {
						strArrHoliday += ",";
					}
					strArrHoliday += "['" + bean.getMm() + "','" + bean.getDd() + "','" + dao.chkData(bean.getYyyy()) + "']";
				}
				strArrHoliday += "]";
			}
			request.setAttribute("strArrHoliday", strArrHoliday);
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
